
import java.io.File;
import java.io.FileInputStream;
import java.sql.DriverManager;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.Statement;
import java.sql.SQLException;
import java.sql.ResultSet;

public class HostDBComm {

    private static Connection conn;
    private Statement stmnt;
    private PreparedStatement viewStatement,  updateStatement,  insertStatement,  videoStatement;
    ResultSet rs;
    Robot robot;

    public HostDBComm() {
        
    }

    public HostDBComm(Robot robot) {
        this.robot = robot;
    }

    protected void connect() {
        try {
            Class.forName("com.mysql.jdbc.Driver");
            conn = DriverManager.getConnection("jdbc:mysql://aasudrl1.db.6601164.hostedresource.com/aasudrl1", "aasudrl1", "Csci1301");
            String viewStmt = "select heading, x, y, leftLine, fwdLine, rightLine from host_to_applet where robot_id = ?";
            viewStatement = conn.prepareStatement(viewStmt);
            String updateStmt = "UPDATE host_to_applet SET heading = ?, x = ?, y = ?, leftLine = ?, fwdLine = ?, rightLine = ? WHERE robot_id = ?";
            updateStatement = conn.prepareStatement(updateStmt);
            String insertStmt = "INSERT INTO host_to_applet (robot_id, heading, x, y, leftLine, fwdLine, rightLine) VALUES (?,?,?,?,?,?,?)";
            insertStatement = conn.prepareStatement(insertStmt);
            if (!conn.isClosed()) {
                stmnt = conn.createStatement(java.sql.ResultSet.TYPE_SCROLL_SENSITIVE, java.sql.ResultSet.CONCUR_UPDATABLE);
            }

            if (!conn.getMetaData().getTables(null, null, "host_to_applet", null).next()) {
                stmnt.execute("create table host_to_applet (robot_id VARCHAR(8), heading VARCHAR(1), x INTEGER, y INTEGER, leftLine INTEGER, fwdLine INTEGER, rightLine INTEGER)");
            }
            if (!conn.getMetaData().getTables(null, null, "applet_to_host", null).next()) {
                stmnt.execute("create table applet_to_host (robot_id VARCHAR(8), heading VARCHAR(1))");
            }
            if (!conn.getMetaData().getTables(null, null, "queue", null).next()) {
                stmnt.execute("create table queue (id VARCHAR(11), position INTEGER, timestamp TIMESTAMP)");
            }
            if (!conn.getMetaData().getTables(null, null, "video", null).next()) {
                stmnt.execute("create table video (name VARCHAR(10), frame BLOB)");
                stmnt.execute("insert into video(name, frame) values('frame',NULL)");
            }
/*
            rs = stmnt.executeQuery("SELECT * FROM video;");
            if (!rs.next()) {
                stmnt.execute("INSERT INTO video(name, frame) VALUES('frame',NULL)");
            } else {
                System.out.println("Theres a video frame in the db");
            }
 */


        } catch (Exception ex) {
            ex.printStackTrace();
        }
    }

    protected byte[] read() {
        try {
            if (!conn.isClosed()) {
                stmnt = conn.createStatement(java.sql.ResultSet.TYPE_SCROLL_SENSITIVE, java.sql.ResultSet.CONCUR_UPDATABLE);
            }
            rs = stmnt.executeQuery("select heading from applet_to_host where robot_id='" + robot.robotID + "';");
            if (rs.next()) {
                byte dir = (byte) (rs.getString(1).charAt(0));
                stmnt.execute("DELETE FROM applet_to_host;");
                System.out.println("Robot: " + robot.robotID + "   Direction: " + dir);
                return new byte[]{1, dir};
            } else {
                try {
                    Thread.sleep(3000);
                } catch (Exception ex) {
                }
                return null;
            }

        } catch (SQLException ex) {
            ex.printStackTrace();
            return null;
        }

    }

    protected void write() {
        try {
            if (!conn.isClosed()) {
                stmnt = conn.createStatement(java.sql.ResultSet.TYPE_SCROLL_SENSITIVE, java.sql.ResultSet.CONCUR_UPDATABLE);
            }
            viewStatement.setString(1, robot.robotID);
            rs = viewStatement.executeQuery();
            if (rs.next()) {
                updateStatement.setString(1, Character.toString(robot.heading));
                updateStatement.setString(2, Integer.toString(robot.point.x));
                updateStatement.setString(3, Integer.toString(robot.point.y));
                updateStatement.setString(4, Integer.toString(robot.leftPath));
                updateStatement.setString(5, Integer.toString(robot.fwdPath));
                updateStatement.setString(6, Integer.toString(robot.rightPath));
                updateStatement.setString(7, robot.robotID);
                int executeUpdate = updateStatement.executeUpdate();
                updateStatement.close();
            } else {
                insertStatement.setString(1, robot.robotID);
                insertStatement.setString(2, Character.toString(robot.heading));
                insertStatement.setString(3, Integer.toString(robot.point.x));
                insertStatement.setString(4, Integer.toString(robot.point.y));
                insertStatement.setString(5, Integer.toString(robot.leftPath));
                insertStatement.setString(6, Integer.toString(robot.fwdPath));
                insertStatement.setString(7, Integer.toString(robot.rightPath));
                int executeInsert = insertStatement.executeUpdate();
                insertStatement.close();
            }
        } catch (SQLException ex) {
            ex.printStackTrace();
        }

    }

    protected void clear() {
        try {
            if (!conn.isClosed()) {
                stmnt = conn.createStatement(java.sql.ResultSet.TYPE_SCROLL_SENSITIVE, java.sql.ResultSet.CONCUR_UPDATABLE);
            }
            stmnt.execute("delete from applet_to_host;");
            stmnt.execute("delete from host_to_applet;");
            stmnt.execute("delete from queue;");
            stmnt.execute("delete from video;");
        } catch (SQLException ex) {
            ex.printStackTrace();
        }

    }

    protected void updateQueueSummary() {
        /*try {
        ResultSet rs = stmnt.executeQuery("select * from queue;");
        ResultSetMetaData r = rs.getMetaData();
        for (int i = 0; i < r.getColumnCount(); i++) {
        System.out.print(r.getColumnName(i));
        }
        System.out.println("");
        } catch (SQLException ex) {
        ex.printStackTrace();
        }
        //Host.queueSummary*/
    }

    protected void disconnect() {
        clear();
        try {
            if (!conn.isClosed()) {
                stmnt.close();
                conn.close();
            }

        } catch (Exception ex) {
            ex.printStackTrace();
        }
    }

    protected void uploadFrame(File image) {
        try {
            videoStatement = conn.prepareStatement("update video set frame=? where name='frame'");
            if (image.length() > 0) {
                FileInputStream fis = new FileInputStream(image);

                videoStatement.setBinaryStream(1, fis, (int) image.length());
                videoStatement.executeUpdate();
                videoStatement.close();
                fis.close();
            }

        } catch (Exception ex) {
            ex.printStackTrace();
        }
    }

    protected void checkStamp() {

        java.sql.Timestamp currentTime = null;
        java.sql.Timestamp clientTime;
        try {
            if (!conn.isClosed()) {
                stmnt = conn.createStatement(java.sql.ResultSet.TYPE_SCROLL_SENSITIVE, java.sql.ResultSet.CONCUR_UPDATABLE);
            }
            ResultSet time = stmnt.executeQuery("SELECT now();");
            if (time.next()) {
                currentTime = time.getTimestamp(1);
            }
            time.close();
            if (!conn.isClosed()) {
                stmnt = conn.createStatement(java.sql.ResultSet.TYPE_SCROLL_SENSITIVE, java.sql.ResultSet.CONCUR_UPDATABLE);
            }

            rs = stmnt.executeQuery("SELECT id, position FROM queue WHERE position = 0;");
            while (rs.next()) {
                String id = rs.getString(1);
                ResultSet rsMax = stmnt.executeQuery("SELECT max(position) FROM queue;");
                rsMax.next();
                int max = rsMax.getInt(1);
                stmnt.executeUpdate("UPDATE queue SET position= " + (max + 1) + " WHERE id = '" + id + "';");
            }

            //rs.close();

            if (!conn.isClosed()) {
                stmnt = conn.createStatement(java.sql.ResultSet.TYPE_SCROLL_SENSITIVE, java.sql.ResultSet.CONCUR_UPDATABLE);
            }

            ResultSet rs2 = stmnt.executeQuery("select * from queue;");

            while (rs2.next()) {
                clientTime = rs2.getTimestamp(3);
                int position = rs2.getInt(2);
                String id = rs2.getString(1);
                if (Math.abs(currentTime.getTime() - clientTime.getTime()) >= 10000) {
                    stmnt.execute("delete from queue where id='" + id + "';"); //Delete the client that has timed out
                    //Repositions all clients behind the client that got deleted.
                    rs2 = stmnt.executeQuery("select * from queue;"); // Need to refresh result. DON'T DELETE
                    if (rs2 == null) {
                        break;
                    }
                    stmnt.executeUpdate("update queue set position=position-1 where position > " + position + ";");
                }
            }
            rs2.close();
        } catch (SQLException ex) {
            ex.printStackTrace();
        }
    }
}
